1. Field of the Invention
The present invention generally relates to systems and methods for navigating relationships between two or more tables or views in a database. More particularly, the present invention relates to systems and methods for graphically representing the navigation of the relationships between two or more tables or views in a database.
2. Description of the Related Art
A database administrator (DBA) has many tasks to perform to maintain a smoothly running database for his/her users. Response time (i.e., performance) of a database is one of the most noticeable elements of the database to a database user. Managing the performance of a database requires a DBA to monitor various aspects of the use of the database.
One example of a relational database in widespread use today is the Oracle DBMS (database management system), which is a product sold by Oracle Corporation. Among the data sources available to an Oracle DBA for monitoring the performance of an Oracle database are a set of xe2x80x9cfixed tablesxe2x80x9d, the data in which is documented and externalized by Oracle Corporation in views known as V$ views. The data in the fixed tables are maintained by an Oracle server and are accessible to users of that database through the V$ views, subject to authentication. The V$ views are so named because each has the prefix V$ as part of its name. The underlying tables are kept continuously up to date while the Oracle database is open and in use, and the contents of these tables relate primarily to performance. Although these tables appear to be regular database tables, they are actually read-only tables. That is, the only action permitted against them is query. Other actions that are normally possible against database tables (e.g., insert, update, and delete) are prohibited.
There are two sets of V$ views. The first set is built directly into the Oracle database engine and is visible only to the special users SYS and INTERNAL. During the standard database creation process Oracle creates a second set of views against the internal or fixed V$ views. This second set of views uses the name prefix V_$, and the creation process grants query rights to these views. Oracle also creates a series of public synonyms for the V_$ views, each with the same name as the original V$ view. By convention, DBAs and other database users access the V$ views through their identically named synonyms rather than through the V_$ objects. Once the instance of the database is started, the V$ views are accessible. The database does not have to be mounted or open, that is data can be retrieved from the V$ views at a time when no normal database objects can be accessed. One important consequence of this fact is that the V$LOG view can be used to identify log files needed for recovery.
A series of views referred to as GV$ views are similar to the V$ views. The GV$ views contain the same information as the V$ views, plus an additional column for the instance ID (inst_id). The GV$ views are used primarily in Oracle Parallel Server environments. A DBA may query the V$ views for the current instance information or query the GV$ views to receive V$ information from all instances, not just the current instance.
Oracle also has a feature that allows the specification of Referential Integrity Constraints. Referential Integrity Constraints allow the person creating a database table to state that certain values may only be present in one table if they are also present in some other table, thereby constraining the set of permitted values. For example each row of a table called TRANSACTIONS might be constrained to ensure that the ACCOUNT_NUMBER within the transaction also existed with a table called ACCOUNTS. The purpose of referential integrity is to prevent database users or applications from entering inconsistent data into a database.
The existence of a Referential Integrity Constraint identifies a parent-child relationship between two database tables. Thus such a constraint may be used to indicate that the database has the capability to store many transactions for a single account, and also to indicate that every transaction within the database will reference an account that is defined within the database. In turn, the existence of such a constraint allows a user or program interrogating the database to assert a formal relationship between two tables that would otherwise be either conjecture or simply not apparent.
Currently, Oracle does not implement referential integrity constraints on either views or on the fixed tables that underlie the V$ views. Due to this lack of relating the various V$ views (and, similarly, the GV$ views) to each other, the DBA""s task of monitoring the performance of the database is made more difficult.
Both DBAs and database users desire the ability to quickly browse and edit data residing in database tables. Typically DBAs are interested in the tables that refer to database administration and performance, whereas the database users are interested in the tables that contain their application data. Examples of instances in which speed of editing would be useful include: populating test tables with data, and making a change in a production environment to solve a problem.
Current methods of browsing and editing data residing in database tables require either scripts to be run or commands to be entered by the user. Typically, one set of scripts or commands may be used to retrieve (i.e., browse) the data, followed by analysis of the retrieved data by the user, and lastly another set of scripts or commands may be issued by the user to edit the data, based on their analysis. This series of steps may be time-consuming and error-prone.
Accordingly, what is needed is a reliable way for either a DBA or a database user to quickly browse and edit data residing in database tables. In addition, it would be helpful to both DBAs and database users if relationships were established between the various V$ views and GV$ views, to ease understanding of the state of the database.
The above limitations of previously known methods of browsing and editing data residing in database tables are overcome by a system and method made in accordance with the principles of the present invention. One embodiment of the method of the present invention includes the steps of providing a graphical user interface in communication with a database, receiving user input to the graphical user interface, retrieving data from the database, displaying the retrieved data in the graphical user interface, and navigating to one or more referenced database tables or views by user request entered on the graphical user interface.
The relationships or references that a database table has to other database tables in the same database is subject to constant change as the records in the database are modified (i.e., inserted, updated, deleted). Referential Integrity is the process of automatically maintaining the correctness or consistency of data in a relational database when modifications are made to fields in a first table, wherein the modified fields are related to other fields in one or more other tables within the same relational database. The purpose of referential integrity is to prevent database users or applications from entering inconsistent data into a database. The goal of referential integrity navigation is to allow the user, having retrieved one or more rows of data, to navigate easily to the data in other tables that either refers to or is referred to by that data. These relationships are defined by referential integrity constraints, and the data thus retrieved by navigation is then available for all the operations possible on the original data, including data editing and further relational navigation. By storing a catalog of the relationships known to exist between Oracle""s V$ views, the same form of navigation can be provided between these data sources.
By automatically displaying to the user via the graphical user interface a listing of the one or more database tables that are referred to by a specific database table, along with the one or more database tables that refer to a specific database table, the referential integrity of the specific database table may be navigated. This navigation allows the user to more rapidly and accurately browse and/or edit the tables of a database. As noted previously, the same concepts of navigation of relationships described for database tables also applies to database views.
Particularly in the Oracle database, the system-created views on the dynamic performance tables referred to as V$ views and GV$ views do not have referential integrity specified within the database. Due to this lack of relating the various V$ views and GV$ views to each other, the DBA""s task of monitoring the performance of the database is made more difficult. One feature of the present invention provides xe2x80x9creferential declarationxe2x80x9d for the purpose of traversing the Oracle V$ views and GV$ views in the same manner as regular database tables may be traversed.